package com.attendance.Utils;

import com.attendance.model.Employee;
import com.attendance.model.Form.ApplyForm;
import com.attendance.model.Form.DepartmentForm;
import com.attendance.model.Form.RecordForm;
import com.attendance.model.Form.VacationForm;
import org.apache.ibatis.jdbc.SQL;

/**
 * Created by inuyasha on 2016/12/13.
 */
public class SQLProvider {

    private final String employeeTable = "employee";
    private final String departmentTable = "department";
    private final String recordTable = "record";
    private final String applyTable = "apply";
    private final String typeTable = "type";
    private final String vacationTable = "vacation";

    public String queryBizEmployee(Employee employee) {
        SQL sql = new SQL().SELECT("employee.id,employee.name,employee.job,employee.sex," +
                "employee.username,employee.password,employee.departmentId,department.name as departmentName")
                .FROM(employeeTable).LEFT_OUTER_JOIN(departmentTable + " on employee.departmentId = department.id");
        if (employee.getId() != null) {
            sql.WHERE("employee.id =  #{id}");
        }
        if (employee.getName() != null) {
            sql.WHERE("employee.name like concat('%',#{name},'%')");
        }
        if (employee.getDepartmentId() != null) {
            sql.WHERE("employee.departmentId =  #{departmentId}");
        }
        if (employee.getUsername() != null) {
            sql.WHERE("employee.username =  #{username}");
        }
        if (employee.getJob() != null) {
            sql.WHERE("employee.job =  #{job}");
        }
        if (employee.getSex() != null) {
            sql.WHERE("employee.sex =  #{sex}");
        }
        return sql.toString();
    }

    public String queryBizDepartment(DepartmentForm departmentForm){
        SQL sql = new SQL().SELECT("department.id,department.name,department.memo," +
                "department.manager,employee.name as managerName")
                .FROM(departmentTable).LEFT_OUTER_JOIN(employeeTable + " on employee.id = department.manager");
        if (departmentForm.getId() != null) {
            sql.WHERE("department.id =  #{id}");
        }
        if (departmentForm.getName() != null) {
            sql.WHERE("department.name like concat('%',#{name},'%')");
        }
        if (departmentForm.getManager() != null) {
            sql.WHERE("department.manager =  #{manager}");
        }
        if (departmentForm.getMemo() != null) {
            sql.WHERE("department.memo like concat('%',#{memo},'%')");
        }
        return sql.toString();
    }

    public String queryBizRecord(RecordForm recordForm){
        SQL sql = new SQL().SELECT("record.id,record.uid,record.record1," +
                "record.record2,record.time,employee.name as employeeName," +
                "department.id as departmentId,department.name as departmentName")
                .FROM(recordTable).FROM(employeeTable).LEFT_OUTER_JOIN(departmentTable + " on employee.departmentId = department.id");
        sql.WHERE("employee.id = record.uid");
        if (recordForm.getId() != null) {
            sql.WHERE("record.id =  #{id}");
        }
        if (recordForm.getUid() != null) {
            sql.WHERE("record.uid = #{uid}");
        }
        if (recordForm.getStart() != null) {
            sql.WHERE("record.time >=  #{start}");
        }
        if (recordForm.getEnd() != null) {
            sql.WHERE("record.time <=  #{end}");
        }
        if (recordForm.getDepartmentId() != null) {
            sql.WHERE("department.id =  #{departmentId}");
        }
        return sql.toString();
    }
    public String queryBizApply(ApplyForm applyForm){
        SQL sql = new SQL().SELECT("apply.id,apply.uid,employee.name as employeeName,apply.startTime,apply.endTime," +
                "apply.state,apply.total,apply.reason," +
                "apply.instructions1,apply.instructions2,apply.instructions3,apply.memo," +
                "apply.type,type.name as typeName,department.id as departmentId,department.name as departmentName")
                .FROM(typeTable).FROM(applyTable).FROM(employeeTable)
                .LEFT_OUTER_JOIN(departmentTable+" on employee.departmentId = department.id");
        sql.WHERE("type.id = apply.type");
        sql.WHERE("employee.id = apply.uid");
        if (applyForm.getId() != null) {
            sql.WHERE("apply.id =  #{id}");
        }
        if (applyForm.getUid() != null) {
            sql.WHERE("apply.uid = #{uid}");
        }
        if (applyForm.getType() != null) {
            sql.WHERE("apply.type = #{type}");
        }
        if (applyForm.getReason() != null) {
            sql.WHERE("apply.reason like concat('%',#{reason},'%')");
        }
        if (applyForm.getState() != null) {
            sql.WHERE("apply.state = #{state}");
        }
        if (applyForm.getStart() != null) {
            sql.WHERE("apply.startTime >= #{start}");
        }
        if (applyForm.getEnd() != null) {
            sql.WHERE("apply.endTime <= #{end}");
        }
        return sql.toString();
    }


    public String queryBizVacation(VacationForm vacationForm){
        SQL sql = new SQL().SELECT("vacation.id,vacation.uid,employee.name as employeeName," +
                "vacation.type,type.name as typeName,vacation.number,vacation.rest")
                .FROM(vacationTable).FROM(employeeTable).FROM(typeTable);
        sql.WHERE("type.id = vacation.type").WHERE("vacation.uid = employee.id");
        if (vacationForm.getId() != null) {
            sql.WHERE("vacation.id =  #{id}");
        }
        if (vacationForm.getUid() != null) {
            sql.WHERE("vacation.uid = #{uid}");
        }
        if (vacationForm.getType() != null) {
            sql.WHERE("vacation.type = #{type}");
        }
        return sql.toString();
    }
}
